package com.softcits.hw.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class DBMetaDataService {

    @Autowired()
    @Qualifier("jdbcTemplate")
    private JdbcTemplate template;
    
    public List<String> getAllTables() throws SQLException {
        DataSource ds = template.getDataSource();
        Connection conn = null;
        try {
            conn = ds.getConnection();
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, "HIBIKI_ADMIN", null, new String[] {"TABLE"});
            List<String> list = new ArrayList<>();
            while(rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String tableSchem = rs.getString("TABLE_SCHEM");
                list.add(tableName);

            }
            return list;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if(conn != null) {
                conn.close();
            }
        }
        
        return null;
    }
    
    public Map<String, String> getDesiredColumns() throws SQLException {
        DataSource ds = template.getDataSource();
        Connection conn = null;
        try {
            conn = ds.getConnection();
            List<String> list = getAllTables();
            Map<String, String> map = new LinkedHashMap<>();
            DatabaseMetaData dbMetaData = conn.getMetaData();
            
            for(String t : list) {
                ResultSet rs = dbMetaData.getColumns(null, "HIBIKI_ADMIN", t, "%");
                while(rs.next()) {
                    String col = rs.getString("COLUMN_NAME");
                    String type = rs.getString("TYPE_NAME");
                    if("clob".equalsIgnoreCase(type) || "blob".equalsIgnoreCase(type)) {
                        map.put(t + ":" + col, type);
                        System.out.println(type + "," + t + "," + col);
                    }
                }
                rs.close();
            }
            
            return map;
           
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if(conn != null) {
                conn.close();
            }
        }
        
        return null;
    }
    
}
